use master
go

create database Students
on
(
	name='Students',
	filename='D:\11\Students.mdf',
	size=5,
	maxsize=50,
	filegrowth=10%
)
log on
(
	name='Students_log',
	filename='D:\11\Students_log.mdf',
	size=5,
	maxsize=50,
	filegrowth=10%
)
go

use Students
go

create table StuInfo
(
	StuNO varchar(5) primary key,
	StuName nvarchar(50) not null,
	StuAge int not null,
	StuAddress nvarchar(50) not null,
	StuSeat int not null,
	StuSex int default(1) check(StuSex='1' or StuSex='0')
)

insert into StuInfo values
('s2501','张秋利','20','美国硅谷','1','1'),
('s2502','李斯文','10','湖北武汉','2','0'),
('s2503','马文才','22','武汉长沙','3','1'),
('s2504','欧阳俊雄','21','湖北武汉','4','0'),
('s2505','梅超风','20','湖北武汉','5','1'),
('s2506','陈旋风','19','美国硅谷','6','1'),
('s2507','陈风','20','美国硅谷','7','0')

create table ExamInfo
(
	examNO int primary key,
	StuNO varchar(5) foreign key references StuInfo(StuNO),
	WittenExam int,
	LabExam int
)

insert into ExamInfo values
('1','s2501','50','70'),
('2','s2502','60','65'),
('3','s2503','86','85'),
('4','s2504','40','80'),
('5','s2505','70','90'),
('6','s2506','85','90')

select * from StuInfo
select * from ExamInfo


--数据如图片1,使用上次作业的数据

--1.查询学生的姓名，年龄，笔试成绩和机试成绩
select StuName,StuAge,WittenExam,LabExam from StuInfo inner join ExamInfo on StuInfo.StuNO = ExamInfo.StuNO
--2.查询笔试和机试成绩都在60分以上的学生的学号，姓名，笔试成绩和机试成绩
select StuInfo.StuNO,StuName,WittenExam,LabExam from StuInfo inner join ExamInfo on StuInfo.StuNO = ExamInfo.StuNO 
where WittenExam>60 and  LabExam>60
--3.查询所有学生的学号，姓名，笔试成绩，机试成绩，没有参加考试的学生的成绩以NULL值填充
select * from StuInfo left join ExamInfo on StuInfo.StuNO = ExamInfo.StuNO
--4.查询年龄在20以上（包括20）的学生的姓名，年龄，笔试成绩和机试成绩，并按笔试成绩降序排列
select StuName,StuAge,WittenExam,LabExam from StuInfo inner join ExamInfo on StuInfo.StuNO = ExamInfo.StuNO 
where StuAge>20 order by WittenExam
--5.查询男女生的机试平均分
select StuSex,AVG(LabExam)平均分 from ExamInfo inner join StuInfo on StuInfo.StuNO = ExamInfo.StuNO group  by StuSex
--6.查询男女生的笔试总分
select StuSex,AVG(WittenExam)平均分 from ExamInfo inner join StuInfo on StuInfo.StuNO = ExamInfo.StuNO group  by StuSex


use Students
go
create table orders
(
	orderId int primary key identity,
	orderDate datetime,
)

create table orderItem
(
	ItemiD int primary key identity,
	orderId int references orders(orderId),
	itemType nvarchar(20),
	itemName nvarchar(20),
	theNumber int,
	theMoney money
)

insert into orders values('2008-01-12 00:00:00.000'),
('2008-02-10 00:00:00.000'),
('2008-02-15 00:00:00.000'),
('2008-03-10 00:00:00.000')
insert into orderItem values(1,'文具','笔',72,'2'),
(1,'文具','尺',10,'1'),
(1,'体育用品','篮球',1,'56'),
(2,'文具','笔',36,'2'),
(2,'文具','固体胶',20,'3'),
(2,'日常用品','透明胶',2,'1'),
(2,'体育用品','羽毛球',20,'3'),
(3,'文具','订书机',20,'3'),
(3,'文具','订书针',20,'3'),
(3,'文具','裁纸刀',5,'5'),
(4,'文具','笔',20,'2'),
(4,'文具','信封',50,'1'),
(4,'日常用品','毛巾',4,'5'),
(4,'日常用品','透明胶',30,'1'),
(4,'体育用品','羽毛球',20,'3')
select * from orders
select * from orderItem
--使用上次作业的订单数据库，完成下列题目：

--1.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价
select * from orderItem inner join orders on orderItem.orderId=orders.orderId
--2.查询订购数量大于50的订单的编号，订单日期，订购产品的类别和订购的产品名称
select orders.orderId,orderDate,itemName,itemType from orderItem inner join orders on orderItem.orderId=orders.orderId
where theNumber>50
--3.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价
select orderItem.orderId,orderDate,itemType,itemName,theNumber,theMoney,theNumber * theMoney 订购总价
from orderItem inner join orders on orderItem.orderId=orders.orderId 
group by orderItem.orderId,orderDate,itemType,itemName,theNumber,theMoney
--4.查询单价大于等于5并且数量大于等于50的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价
select orderItem.orderId,orderDate,itemType,itemName,theNumber,theMoney,theNumber * theMoney 订购总价
from orderItem inner join orders on orderItem.orderId=orders.orderId where theMoney>5 and theNumber>50
group by orderItem.orderId,orderDate,itemType,itemName,theNumber,theMoney
--5.查询每个订单分别订购了几个产品，例如：
--			编号   订购产品数
--      	  1           3
--            2           4
select orderID,sum(theNumber) 订购产品数 from orderItem group by orderId
--6.查询每个订单里的每个类别的产品分别订购了几次和总数量，例如：

-- 订单编号       产品类别     订购次数     总数量

--    1           文具            	2           82
--    1           体育用品        	1            1
--    2           文具            	2           56
--    2           体育用品        	1            2
    --2           日常用品        	1            20
select * from orderItem
select orderId,itemType,count(ItemName) 订购次数,sum(theNumber) 总数量 from orderItem group by orderId,itemType


use Students
go
create table bbsUsers
(
	uID int identity(1,1) not null ,
	uName nvarchar(10) not null,
	uSex  varchar(2) not null,
	uAge  int not null,
	uPoint  int not null

)
alter table bbsUsers add constraint PK_bbsUsers_uID primary key(uID)
alter table bbsUsers add constraint UK_bbsUsers_uName unique(uName)
alter table bbsUsers add constraint CK_bbsUsers_uSex check(uSex='男' or uSex='女')
alter table bbsUsers add constraint CK_bbsUsers_uAge check(uAge>=15 or uAge<=60)
alter table bbsUsers add constraint CK_bbsUsers_uPoint check(uPoint>=0)
create table bbsSection
(
	sID  int  identity(1,1) not null ,
	sName  varchar(10) not null,
	sUid   int ,

)
alter table bbsSection add constraint PK_bbsSection_sID primary key(sID)
alter table bbsSection add constraint FK_bbsSection_sUid foreign key(sUid)references bbsUsers(uID)
create table bbsTopic
(
	tID  int primary key identity(1,1) not null,
	tUID  int references bbsUsers(uID) not null  ,
    tSID int references  bbsSection(sID) ,
	tTitle  varchar(100) not null,
	tMsg  text  not null,
	tTime  datetime  not null,
	tCount  int not null,
)
create table bbsReply
(
	rID  int primary key identity(1,1) not null,
	rUID int references bbsUsers(uID) ,
	rTID  int references bbsTopic(tID) ,
	rMsg  text  not null,
	rTime  datetime ,
)
go
use Students
go
select * from bbsSection
select * from bbsReply
select * from bbsTopic
select * from bbsUsers
insert into bbsUsers values('小雨点','女',20,0),('逍遥','男',18,4),('七年级生','男',19,2)
insert into bbsSection values('技术名流',1),('读书世界',3),('生活百科',1),('八卦区',3)
insert into bbsTopic values(2,4,'范跑跑','谁是范跑跑', 2008-7-8,1),(3,2,'.NET','与JAVA的区别是什么呀？',2008-9-1,2),(1,4,'今年夏天最流行什么','有谁知道今年夏天最流行什么呀？',2008-9-10,0)
insert into bbsReply values(2,2,'一名地震自己先跑的教师',2008-7-8),(3,3,'不知道',2008-9-1),(1,1,'流行穿黑裙子',2008-9-10)
update  bbsUsers set  uPoint=30 where uName='小雨点'
--在论坛数据库中完成以下题目
--1.查询出每个版块的版主编号，版主姓名和版块名称
select tID,sName,uName from bbsTopic inner join bbsSection on sID=tID 
inner join bbsUsers on tUID=uID
--2.查询出主贴的发帖时间在2008-9-15以后的主贴的发帖人编号，发帖人姓名，帖子的标题，帖子的内容和发帖时间
select tID,uName,tTitle,tMsg,tTime from bbsTopic inner join bbsUsers on tUID=uID where tTime>'2008-9-15'
--3.查询出年龄在20以下的版主的编号，版主的名称和版块的名称
select tID,sName,uName from bbsTopic inner join bbsSection on sID=tID inner join bbsUsers on tUID=uID
where uAge<20
--4.查询出回复数量最多的主贴的发帖人编号，发帖人姓名，主贴标题，主贴内容和回复数量
select top 1 tID,uName,tTitle,tMsg,tCount from bbsTopic inner join bbsUsers on tUID=uID order by tCount DESC
--5.在主贴表中查询每个版块中每个用户的发帖总数
select tSID 版块,tUID 用户,count(tSID) 发帖总数 from bbsTopic inner join bbsSection on sID=tID inner join bbsUsers on tUID=uID 
group by tSID,tUID

